from pymysql import *


class JD(object):

    def __init__(self) -> None:
        self.conn = connect(host='47.105.98.103', port=3306, user='root', password='root', database='py_study')
        self.cursor = self.conn.cursor()

    def __del__(self):
        # 释放资源
        self.cursor.close()
        self.conn.close()

    def exec_sql(self, sql):
        """执行查询并打印"""
        self.cursor.execute(sql)
        for item in self.cursor.fetchall():
            print(item)

    def show_all_items(self):
        """查询所有商品"""
        self.exec_sql("select name from goods")

    def show_all_cate(self):
        """查询所有分类"""
        self.exec_sql("select name from goods_cates")

    def add_category(self):
        """添加类目"""
        name = input("请输入类目名称")
        sql = "insert into py_study.goods_cates (name) value ('%s')" % name
        self.cursor.execute(sql)
        self.conn.commit()

    def show_all_brand(self):
        """查询所有品牌"""
        self.exec_sql("select name from goods_brand")

    def select_item_by_name(self, item_name):
        """防止sql注入,不再自行拼装sql"""
        sql = "select * from goods where name=%s"
        self.cursor.execute(sql, [item_name])
        print(self.cursor.fetchall())

    @staticmethod
    def print_menu():
        print("----------请选择功能----------")
        print("1.查询所有商品")
        print("2.查询所有分类")
        print("3.查询所有品牌")
        print("4.添加类目")
        print("5.按照名称查询商品")
        return input("请输入功能序号")

    def run(self):
        while True:
            num = self.print_menu()
            if num == "1":
                self.show_all_items()
            elif num == "2":
                self.show_all_cate()
            elif num == "3":
                self.show_all_brand()
            elif num == "4":
                self.add_category()
            elif num == "5":
                self.select_item_by_name("商务双肩背包")
            else:
                print("请输入正确的命令")


def main():
    jd = JD()
    jd.run()


if __name__ == '__main__':
    main()
